Querying Data

This lesson teaches how to query for data in MySQL.

We'll cover the following

Querying Data#

In this lesson we’ll learn how to query the data we have stored in a table. The SELECT statement allows us to retrieve data from tables.

Example Syntax#

SELECT col1, col2, … coln

FROM table

WHERE <condition>

Connect to the terminal below by clicking in the widget. Once connected, the command line prompt will show up. Enter or copy and paste the command ./DataJek/Lessons/8lesson.sh and wait for the MySQL prompt to start-up.

Terminal 1
Terminal

Click to Connect...

  1. Execute the following SELECT statement to retrieve all the rows in the table with all the columns.

    SELECT * from Actors;

The command reads all the data in the table. The syntax for a simple SELECT command is as follows:

SELECT <columns> FROM <TableName>

The SELECT keyword is followed by a comma-separated list of columns we wish to display. Using an * displays all the columns. The table to query is specified using the FROM keyword followed by the table name.

  1. In the next command, we’ll display the first name and second name columns. Execute the following command:

    SELECT FirstName, SecondName from Actors;

    The outcome is as follows:

The columns are displayed in the same order as they appear in the MYSQL query.

  1. We can filter the rows for a select query using the WHERE clause. The WHERE clause specifies a criterion that rows must match to be returned by the SELECT query. The criteria may be met by zero, one, multiple, or all rows.

    Executing the following query will result in no row being matched:

    SELECT FirstName, SecondName from Actors WHERE FirstName="Travolta";

Executing the following query will result in exactly one row being matched:

SELECT FirstName, SecondName from Actors WHERE FirstName="Brad";

Executing the following query results in multiple rows being matched:

SELECT FirstName, SecondName from Actors WHERE NetWorthInMillions > 500;

Finally, executing the following query results in all rows being matched and returned:

SELECT FirstName, SecondName from Actors WHERE NetWorthInMillions > 0;

The following table captures the various operators that can be used in a WHERE clause.

Operator Purpose
> Greater than operator
>= Greater than or equal to operator
< Less than operator
<= Less than or equal to operator
!= Not equal operator
<> Not equal operator
<=> NULL-safe equal to operator
= Equal to operator
BETWEEN … AND … Whether a value is within a range of values
COALESCE() Return the first non-NULL argument
GREATEST() Return the largest argument
IN Whether a value is within a set of values
INTERVAL Return the index of the argument that is greater than the first argument
IS Test a value against a boolean
IS NOT Test a value against a boolean
IS NOT NULL NOT NULL value test
IS NULL NULL value test
ISNULL() Test whether the argument is NULL
LEAST() Return the smallest argument
LIKE Simple pattern matching
NOT BETWEEN … AND … Whether a value is not within a range of values
NOT IN() Whether a value is not within a set of values
NOT LIKE Negation of simple pattern matching
STRCMP() Compare two strings
Inserting Data
LIKE Operator
Mark as Completed
Report an Issue